package dao.oracle;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import transferobject.Bid;
import transferobject.FullInfo;
import transferobject.Item;
import transferobject.User;
import dao.FullInfoDao;

/**
 * FullInfo DAO for Oracle database
 * @author Ilya_Poliakov
 */
public class OracleFullInfoDao implements FullInfoDao {

	/**
	 * FullInfo fields
	 * @author Ilya_Poliakov
	 */
	public enum FullInfoFields {
		/**
		 * BID column
		 */
		BID,
		/**
		 * BID_ID column
		 */
		BID_ID,
		/**
		 * BID_INCREMENT column
		 */
		BID_INCREMENT,
		/**
		 * BIDDER_ADDRESS column
		 */
		BIDDER_ADDRESS,
		/**
		 * BIDDER_ID column
		 */
		BIDDER_ID,
		/**
		 * BIDDER_LOGIN column
		 */
		BIDDER_LOGIN,
		/**
		 * BIDDER_NAME column
		 */
		BIDDER_NAME,
		/**
		 * BIDDER_PASSWORD column
		 */
		BIDDER_PASSWORD,
		/**
		 * BUY_IT_NOW column
		 */
		BUY_IT_NOW,
		/**
		 * DESCRIPTION column
		 */
		DESCRIPTION,
		/**
		 * ITEM_ID column
		 */
		ITEM_ID,
		/**
		 * SELLER_ADDRESS column
		 */
		SELLER_ADDRESS,
		/**
		 * SELLER_ID column
		 */
		SELLER_ID,
		/**
		 * SELLER_LOGIN column
		 */
		SELLER_LOGIN,
		/**
		 * SELLER_NAME column
		 */
		SELLER_NAME,
		/**
		 * SELLER_PASSWORD column
		 */
		SELLER_PASSWORD,
		/**
		 * START_BIDDING_DATE column
		 */
		START_BIDDING_DATE,
		/**
		 * START_PRICE column
		 */
		START_PRICE,
		/**
		 * TIMELEFT column
		 */
		TIMELEFT,
		/**
		 * TITLE column
		 */
		TITLE
	}

	private static final String ASC_STRING = "ASC";
	private static final String BID_FIELD_NAME = "bid";
	private static final String BID_ID_FIELD_NAME = "bid_id";
	private static final String BID_INCREMENT_FIELD_NAME = "bid_increment";
	private static final String BIDDER_ADDRESS_FIELD_NAME = "bidder_address";
	private static final String BIDDER_ID_FIELD_NAME = "bidder_id";
	private static final String BIDDER_LOGIN_FIELD_NAME = "bidder_login";
	private static final String BIDDER_NAME_FIELD_NAME = "bidder_name";
	private static final String BIDDER_PASSWORD_FIELD_NAME = "bidder_password";
	private static final String BUY_IT_NOW_FIELD_NAME = "buy_it_now";

	private static final String DESC_STRING = "DESC";
	private static final String DESCRIPTION_FIELD_NAME = "description";
	private static final String GET_FULL_INFO_ITEM = "SELECT * FROM full_info WHERE item_id = ?";
	private static final String GET_FULL_INFO_QUERY = "SELECT * " +
			"FROM (SELECT finfo.*, rownum as rNum " +
			"FROM (SELECT * FROM full_info WHERE lower(title) LIKE lower(?) " +
			"ORDER BY %s) finfo) WHERE rNum BETWEEN ? AND ?";
	private static final String ITEM_ID_FIELD_NAME = "item_id";

	private static final String SELLER_ADDRESS_FIELD_NAME = "seller_address";
	private static final String SELLER_ID_FIELD_NAME = "seller_id";

	private static final String SELLER_LOGIN_FIELD_NAME = "seller_login";
	private static final String SELLER_NAME_FIELD_NAME = "seller_name";
	private static final String SELLER_PASSWORD_FIELD_NAME = "seller_password";
	private static final String START_BIDDING_DATE_FIELD_NAME = "start_bidding_date";
	private static final String START_PRICE_FIELD_NAME = "start_price";
	private static final String TIME_LEFT_FIELD_NAME = "time_left";
	private static final String TITLE_FIELD_NAME = "title";
	private final OracleConnection connection;

	/**
	 * create new OracleBidDao
	 * @param connection OracleConnection object
	 */
	public OracleFullInfoDao(OracleConnection connection) {
		super();
		this.connection = connection;
	}

	/*
	 * Return the column name of DB table by the enum value
	 * @param field enum value
	 * @return the column name of DB table by the enum value
	 */
	private String getFieldName(FullInfoFields field) {
		switch(field) {
		case ITEM_ID:
			return "ITEM_ID";
		case TITLE:
			return "TITLE";
		case DESCRIPTION:
			return "DESCRIPTION";
		case START_PRICE:
			return "START_PRICE";
		case TIMELEFT:
			return "TIME_LEFT";
		case START_BIDDING_DATE:
			return "START_BIDDING_DATE";
		case BUY_IT_NOW:
			return "BUY_IT_NOW";
		case BID_INCREMENT:
			return "BID_INCREMENT";
		case SELLER_ID:
			return "SELLER_ID";
		case SELLER_NAME:
			return "SELLER_NAME";
		case SELLER_ADDRESS:
			return "SELLER_ADDRESS";
		case SELLER_LOGIN:
			return "SELLER_LOGIN";
		case SELLER_PASSWORD:
			return "SELLER_PASSWORD";
		case BID_ID:
			return "BID_ID";
		case BID:
			return "BID";
		case BIDDER_ID:
			return "BIDDER_ID";
		case BIDDER_NAME:
			return "BIDDER_NAME";
		case BIDDER_ADDRESS:
			return "BIDDER_ADDRESS";
		case BIDDER_LOGIN:
			return "BIDDER_LOGIN";
		case BIDDER_PASSWORD:
			return "BIDDER_PASSWORD";
		default:
			return "ITEM_ID";
		}
	}

	/*
	 * Return FullInfo object from ResultSet row
	 * @param rs ResultSet object
	 * @return FullInfo object
	 */
	private FullInfo getFullInfoFromResultSet(ResultSet rs) {
		FullInfo fullItem = null;
		try {
			if (rs.next()) {

				int 	itemId 				= rs.getInt(ITEM_ID_FIELD_NAME);
				int 	sellerId 			= rs.getInt(SELLER_ID_FIELD_NAME);
				String 	title 				= rs.getString(TITLE_FIELD_NAME);
				String 	description 		= rs.getString(DESCRIPTION_FIELD_NAME);
				float 	startPrice 			= rs.getFloat(START_PRICE_FIELD_NAME);
				int 	timeLeft 			= rs.getInt(TIME_LEFT_FIELD_NAME);
				Date 	startBiddingDate 	= rs.getTimestamp(START_BIDDING_DATE_FIELD_NAME);
				boolean buyItNow 			= rs.getInt(BUY_IT_NOW_FIELD_NAME) == 1;
				float 	bidIncrement 		= rs.getFloat(BID_INCREMENT_FIELD_NAME);
				Item item = new Item(itemId, sellerId, title, description,
						startPrice, timeLeft, startBiddingDate,
						buyItNow, bidIncrement);

				String 	sellerName 		= rs.getString(SELLER_NAME_FIELD_NAME);
				String 	sellerAddress 	= rs.getString(SELLER_ADDRESS_FIELD_NAME);
				String 	sellerLogin 	= rs.getString(SELLER_LOGIN_FIELD_NAME);
				String 	sellerPassword 	= rs.getString(SELLER_PASSWORD_FIELD_NAME);
				User seller = new User(sellerId, sellerName, sellerAddress,
						sellerLogin, sellerPassword);

				int 	bidId 		= rs.getInt(BID_ID_FIELD_NAME);
				int 	bidderId 	= rs.getInt(BIDDER_ID_FIELD_NAME);
				float 	bidValue 	= rs.getFloat(BID_FIELD_NAME);
				Bid bid = new Bid(bidId, bidderId, bidValue, itemId);

				String 	bidderName = rs.getString(BIDDER_NAME_FIELD_NAME);
				String	bidderAddress = rs.getString(BIDDER_ADDRESS_FIELD_NAME);
				String	bidderLogin = rs.getString(BIDDER_LOGIN_FIELD_NAME);
				String	bidderPassword = rs.getString(BIDDER_PASSWORD_FIELD_NAME);
				User bidder = new User(bidderId, bidderName, bidderAddress,
						bidderLogin, bidderPassword);
				fullItem = new FullInfo(item, seller, bid, bidder);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return fullItem;
	}

	/**
	 * {@inheritDoc}
	 */
	public FullInfo getFullInfoItem(int itemId) {
		FullInfo item = null;
		try {
			Connection cn = connection.getConnection();
			PreparedStatement ps = cn.prepareStatement(GET_FULL_INFO_ITEM);
			ps.setInt(1, itemId);
			ResultSet rs = ps.executeQuery();
			item = getFullInfoFromResultSet(rs);
			rs.close();
			ps.close();
			cn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return item;
	}

	/**
	 * {@inheritDoc}
	 */
	public ArrayList<FullInfo> getFullInfoItems(FullInfoFields sortingField,
			Boolean isAsc, int pageNumber, int itemsCountOnPage,
			String searchString) {
		ArrayList<FullInfo> items = new ArrayList<FullInfo>();
		try {
			Connection cn = connection.getConnection();
			String query = String.format(GET_FULL_INFO_QUERY,
					getFieldName(sortingField) + ' '
							+ (isAsc ? ASC_STRING : DESC_STRING));
			PreparedStatement ps = cn.prepareStatement(query);
			int pageStartPosition = (pageNumber - 1) * itemsCountOnPage + 1;
			int pageEndPosition = pageStartPosition + itemsCountOnPage - 1;
			ps.setString(1, '%' + searchString + '%');
			ps.setInt(2, pageStartPosition);
			ps.setInt(3, pageEndPosition);
			ResultSet rs = ps.executeQuery();
			FullInfo item;
			while ((item = getFullInfoFromResultSet(rs)) != null) {
				items.add(item);
			}
			cn.commit();
			rs.close();
			ps.close();
			cn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return items;
	}
}
